﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using Model;
namespace DAL
{
public     class InvestDAL
    {
    /// <summary>
    /// 显示建立链接
    /// </summary>
      SqlConnection con = new SqlConnection("Data Source=PC201609040948;Initial Catalog=TianTianDai_Db;Persist Security Info=True;User ID=sa;pwd=123456");
    /// <summary>
    /// 投资的显示
    /// </summary>
    /// <returns></returns>
        public List<Invest>  Invest_Show()
      {
          con.Open();
          string sql = "select * from Invest";
          SqlDataAdapter dap = new SqlDataAdapter(sql, con);
          DataTable table = new DataTable();
          dap.Fill(table);
            con.Close();
            List<Invest> list = new List<Invest>();
            foreach (DataRow item in table.Rows)
            {
                Invest i = new Invest();
                i.Invester = item["Invester"].ToString();
                i.InvestGain = Convert.ToDecimal(item["InvestGain"]);
                i.InvestId = Convert.ToInt32(item["InvestId"]);
                i.InvestMoney = Convert.ToDecimal(item["InvestMoney"]);
                i.InvestRaterate = item["InvestRaterate"].ToString();
                list.Add(i);
            }
            return list;
      }
        /// <summary>
        /// 投资的添加
        /// </summary>
        /// <param name="m"></param>
        /// <returns></returns>
        public int Invest_Insert(Invest u)
        {
            con.Open();
            string sql = "inset into UserInfo values ('" + u.Invester + "','" + u.InvestGain + "','" + u.InvestId + "','" + u.InvestMoney + "','" + u.InvestRaterate + "')";
            SqlCommand com = new SqlCommand(sql, con);
            return com.ExecuteNonQuery();
        }
        /// <summary>
        /// 投资的删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Invest_Delete(int id)
        {
            con.Open();
            string sql = "delete Invest where InvestId='" + id + "'";
            SqlCommand com = new SqlCommand(sql, con);
            return com.ExecuteNonQuery();
        }
        /// <summary>
        /// 投资的显示或修改第一步
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Invest Invest_details(int id)
        {
            con.Open();
            string sql = "select * from Invest Where InvestId='" + id + "'";
            SqlDataAdapter dap = new SqlDataAdapter(sql, con);
            DataTable table = new DataTable();
            dap.Fill(table);
            con.Close();
            List<Invest> list = new List<Invest>();
            foreach (DataRow item in table.Rows)
            {
                Invest i = new Invest();
                i.Invester = item["Invester"].ToString();
                i.InvestGain = Convert.ToDecimal(item["InvestGain"]);
                i.InvestId = Convert.ToInt32(item["InvestId"]);
                i.InvestMoney = Convert.ToDecimal(item["InvestMoney"]);
                i.InvestRaterate = item["InvestRaterate"].ToString();
                list.Add(i);
            }
            
            return list.FirstOrDefault();
        }
        /// <summary>
        /// 投资修改
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Invest_Edit(Invest u)
        {
            con.Open();
            string sql = "Update Invest set  Invester='" + u.Invester + "',InvestGain='" + u.InvestGain + "',InvestMoney='" + u.InvestMoney + "',InvestRaterate='" + u.InvestRaterate + "'";
            SqlCommand com = new SqlCommand(sql, con);
            return com.ExecuteNonQuery();
        }
      /// <summary>
        /// 投资的查询
      /// </summary>
      /// <param name="Invester"></param>
      /// <returns></returns>
        public List<Invest> Invest_Search(string Invester)
        {
            con.Open();
            string sql = "select Invester ,InvestGain,InvestMoney,InvestRaterate,InvestId from Invest where Invester like'" + Invester + "'";
            SqlDataAdapter dap = new SqlDataAdapter(sql, con);
            DataTable table = new DataTable();
            dap.Fill(table);
            con.Close();
            List<Invest> list = new List<Invest>();
            foreach (DataRow item in table.Rows)
            {
                Invest i = new Invest();
                i.Invester = item["Invester"].ToString();
                i.InvestGain = Convert.ToDecimal(item["InvestGain"]);
                i.InvestId = Convert.ToInt32(item["InvestId"]);
                i.InvestMoney = Convert.ToDecimal(item["InvestMoney"]);
                i.InvestRaterate = item["InvestRaterate"].ToString();
                list.Add(i);
            }
            return list;
        }
    }
}
